package sg.edu.nus.iss.phoenix.authenticate.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import sg.edu.nus.iss.phoenix.authenticate.dao.UserDao;
import sg.edu.nus.iss.phoenix.authenticate.entity.User;
import sg.edu.nus.iss.phoenix.core.dao.DBConstants;
import sg.edu.nus.iss.phoenix.core.exceptions.NotFoundException;



/**
 * User Data Access Object (DAO). This class contains all database handling that
 * is needed to permanently store and retrieve User object instances.
 * 
 * @author Swami Nathan Antony Swami(A0092692W)
 * @version 1.0 2012/9/20
*/ 
public class UserDaoImpl implements UserDao {
	Connection connection;

	public UserDaoImpl() {
		super();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#createValueObject()
	 */
	@Override
	public User createValueObject() {
		return new User();
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#getObject(java.sql
	 * .Connection, int)
	 */
	@Override
	public User getObject(String id) throws NotFoundException, SQLException {

		User valueObject = createValueObject();
		valueObject.setId(id);
		load(valueObject);
		return valueObject;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#load(java.sql.Connection
	 * , sg.edu.nus.iss.phoenix.authenticate.entity.User)
	 */
	@Override
	public User load(User valueObject) throws NotFoundException, SQLException {

		String sql = "SELECT * FROM user WHERE (`user_id` = ? ) ";
		PreparedStatement stmt = null;
		
		try {
			openConnection();
			stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, valueObject.getId());

			singleQuery(stmt, valueObject);

		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
		return valueObject;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#loadAll(java.sql
	 * .Connection)
	 */
	@Override
	public List<User> loadAll() throws SQLException {

		String sql = "SELECT * FROM user ORDER BY user_id ASC ";
		openConnection();
		List<User> searchResults = listQuery(this.connection
				.prepareStatement(sql));
		closeConnection();
		return searchResults;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#create(java.sql.
	 * Connection, sg.edu.nus.iss.phoenix.authenticate.entity.User)
	 */
	@Override
	public synchronized void create(User valueObject) throws SQLException {

		String sql = "";
		PreparedStatement stmt = null;
		try {
			openConnection();
			sql = "INSERT INTO user ( user_id, password, username, first_name, last_name) VALUES (?, ?, ?, ?, ?) ";
			stmt = this.connection.prepareStatement(sql,
					Statement.RETURN_GENERATED_KEYS);

			stmt.setString(1, valueObject.getId());
			stmt.setString(2, valueObject.getPassword());
			stmt.setString(3, valueObject.getName());
			stmt.setString(4, valueObject.getFirstName());
			stmt.setString(5, valueObject.getLastName());
			// stmt.setString(4, valueObject.getRoles().get(0).getRole());

			int user_id = databaseUpdate(stmt, true);
			valueObject.setId(String.valueOf(user_id));

			if (user_id <= 0) {
				// // System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when inserting user!");
			}

		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
			
		}

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#save(java.sql.Connection
	 * , sg.edu.nus.iss.phoenix.authenticate.entity.User)
	 */
	@Override
	public void save(User valueObject) throws NotFoundException, SQLException {

		String sql = "UPDATE user SET password = ?, username = ?, first_name = ? , last_name = ?  WHERE (user_id = ? ) ";
		PreparedStatement stmt = null;

		try {
			openConnection();
	     	stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, valueObject.getPassword());
			stmt.setString(2, valueObject.getName());
			stmt.setString(3, valueObject.getFirstName());
			stmt.setString(4, valueObject.getLastName());
			stmt.setString(5, valueObject.getId());

			

			int rowcount = databaseUpdate(stmt, false);
			if (rowcount == 0) {
				// // System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// // System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#delete(java.sql.
	 * Connection, sg.edu.nus.iss.phoenix.authenticate.entity.User)
	 */
	@Override
	public void delete(User valueObject) throws NotFoundException, SQLException {

		String sql = "DELETE FROM user WHERE (user_id = ? ) ";
		PreparedStatement stmt = null;

		try {
			openConnection();
			stmt = this.connection.prepareStatement(sql);
			stmt.setString(1, valueObject.getId());

			int rowcount = databaseUpdate(stmt , false);
			if (rowcount == 0) {
				// // System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new NotFoundException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// // System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#deleteAll(java.sql
	 * .Connection)
	 */
	@Override
	public void deleteAll() throws SQLException {

		String sql = "DELETE FROM user";
		PreparedStatement stmt = null;

		try {
			openConnection();
			stmt = this.connection.prepareStatement(sql);
			int rowcount = databaseUpdate(stmt, false);
		} finally {
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#countAll(java.sql
	 * .Connection)
	 */
	@Override
	public int countAll() throws SQLException {

		String sql = "SELECT count(*) FROM user";
		PreparedStatement stmt = null;
		ResultSet result = null;
		int allRows = 0;

		try {
			openConnection();
			stmt = this.connection.prepareStatement(sql);
			result = stmt.executeQuery();

			if (result.next())
				allRows = result.getInt(1);
		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			closeConnection();
		}
		return allRows;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * sg.edu.nus.iss.phoenix.authenticate.dao.impl.UserDao#searchMatching(java
	 * .sql.Connection, sg.edu.nus.iss.phoenix.authenticate.entity.User)
	 */
	@Override
	public List<User> searchMatching(User valueObject) throws SQLException {

		List<User> searchResults;

		boolean first = true;
		StringBuffer sql = new StringBuffer("SELECT * FROM user WHERE 1=1 ");

		if (valueObject.getId() != "") {
			if (first) {
				first = false;
			}
			sql.append("AND id = ").append(valueObject.getId()).append(" ");
		}

		if (valueObject.getPassword() != null) {
			if (first) {
				first = false;
			}
			sql.append("AND password LIKE '").append(valueObject.getPassword())
					.append("%' ");
		}

		if (valueObject.getName() != null) {
			if (first) {
				first = false;
			}
			sql.append("AND name LIKE '").append(valueObject.getName())
					.append("%' ");
		}

		if (valueObject.getRoles().get(0).getRoleName() != null) {
			if (first) {
				first = false;
			}
			sql.append("AND role LIKE '")
					.append(valueObject.getRoles().get(0).getRoleName())
					.append("%' ");
		}

		sql.append("ORDER BY id ASC ");

		// Prevent accidential full table results.
		// Use loadAll if all rows must be returned.
		openConnection();
		if (first)
			searchResults = new ArrayList<User>();
		else
			searchResults = listQuery(this.connection.prepareStatement(sql
					.toString()));
		closeConnection();

		return searchResults;
	}

	/**
	 * databaseUpdate-method. This method is a helper method for internal use.
	 * It will execute all database handling that will change the information in
	 * tables. SELECT queries will not be executed here however. The return
	 * value indicates how many rows were affected. This method will also make
	 * sure that if cache is used, it will reset when data changes.
	 * 
	 * @param conn
	 *            This method requires working database connection.
	 * @param stmt
	 *            This parameter contains the SQL statement to be excuted.
	 */
	protected int databaseUpdate(PreparedStatement stmt,
			boolean returnGeneratedKey) throws SQLException {
		
		int result = stmt.executeUpdate();

		if (returnGeneratedKey == true) {
			ResultSet keys = stmt.getGeneratedKeys();

			if (keys != null) {
				keys.next();
				return keys.getInt(1);
			}
			
		}

		return result;
	}

	/**
	 * databaseQuery-method. This method is a helper method for internal use. It
	 * will execute all database queries that will return only one row. The
	 * resultset will be converted to valueObject. If no rows were found,
	 * NotFoundException will be thrown.
	 * 
	 * @param conn
	 *            This method requires working database connection.
	 * @param stmt
	 *            This parameter contains the SQL statement to be excuted.
	 * @param valueObject
	 *            Class-instance where resulting data will be stored.
	 */
	protected void singleQuery(PreparedStatement stmt, User valueObject)
			throws NotFoundException, SQLException {

		ResultSet result = null;
		
		try {
			result = stmt.executeQuery();

			if (result.next()) {

				valueObject.setId(result.getString("user_id"));
				valueObject.setPassword(result.getString("password"));
				valueObject.setName(result.getString("username"));
				valueObject.setFirstName(result.getString("first_name"));
				valueObject.setLastName(result.getString("last_name"));
				// Role e = new Role(result.getString("role"));
				// ArrayList<Role> roles = new ArrayList<Role>();
				// roles.add(e);
				// valueObject.setRoles(roles);

			} else {
				// // System.out.println("User Object Not Found!");
				throw new NotFoundException("User Object Not Found!");
			}
		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			
		}
	}

	/**
	 * databaseQuery-method. This method is a helper method for internal use. It
	 * will execute all database queries that will return multiple rows. The
	 * resultset will be converted to the List of valueObjects. If no rows were
	 * found, an empty List will be returned.
	 * 
	 * @param conn
	 *            This method requires working database connection.
	 * @param stmt
	 *            This parameter contains the SQL statement to be excuted.
	 */
	protected List<User> listQuery(PreparedStatement stmt) throws SQLException {

		List<User> searchResults = new ArrayList<User>();
		RoleDaoImpl roleImp = new RoleDaoImpl();
		ResultSet result = null;

		try {
			
			result = stmt.executeQuery();

			while (result.next()) {
				User temp = createValueObject();

				temp.setId(result.getString("user_id"));
				temp.setPassword(result.getString("password"));
				temp.setName(result.getString("username"));
				temp.setFirstName(result.getString("first_name"));
				temp.setLastName(result.getString("last_name"));

				// ArrayList<Role> roles = new ArrayList<Role>();
				// try {
				// roles = (ArrayList<Role>) roleImp.getUserRole(temp);
				// } catch (NotFoundException e) {
				// // TODO Auto-generated catch block
				// e.printStackTrace();
				// }
				// temp.setRoles(roles);

				searchResults.add(temp);
			}

		} finally {
			if (result != null)
				result.close();
			if (stmt != null)
				stmt.close();
			
		}

		return (List<User>) searchResults;
	}

	private Connection openConnection() {
		Connection conn = null;
		try {
			Class.forName(DBConstants.COM_MYSQL_JDBC_DRIVER);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		try {
			this.connection = DriverManager.getConnection(DBConstants.dbUrl,
					DBConstants.dbUserName, DBConstants.dbPassword);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	
	private void closeConnection() {
		try {
			this.connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
